-- 1.项目成本核算结果明细表cost.pro_cost_cal_res_detail改为分区表
-- 获取方案id最大值
declare @maxNum int = 1;
select @maxNum = isnull(ident_current('cost.pro_check_scheme'), 0);

-- 创建分区函数
declare @funNum int = 1;
declare @part_id_str nvarchar(max) = '';
declare @part_fun_sql nvarchar(max) = 'create partition function pro_crd_part_fun(int) as range left for values(';
begin

	while @funNum <= @maxNum
    begin
	   set @part_id_str = concat(@part_id_str, cast(@funNum as varchar(3)), ',');
       set @funNum = @funNum + 1;
    end;

	set @part_id_str = left(@part_id_str, len(@part_id_str) - 1);
	set @part_fun_sql = concat(@part_fun_sql, @part_id_str, ');');

	exec sp_executesql @part_fun_sql;
end;

-- 创建分区方案
declare @schemeNum int = 1;
declare @scheme_id_str nvarchar(max) = '';
declare @scheme_sql nvarchar(max) = 'create partition scheme pro_crd_scheme as partition pro_crd_part_fun to(';
begin

	while @schemeNum <= (@maxNum + 1)
    begin
	   set @scheme_id_str = concat(concat(@scheme_id_str, 'pcsi_'), cast(@schemeNum as varchar(3)), ',');
       set @schemeNum = @schemeNum + 1;
    end;

	set @scheme_id_str = left(@scheme_id_str, len(@scheme_id_str) - 1);
	set @scheme_sql = concat(@scheme_sql, @scheme_id_str, ');');

	exec sp_executesql @scheme_sql;
end;

-- 创建分区表
create table cost.pro_cost_cal_res_detail_new(
  id int identity(1,1) not null,
  cal_res_id int  null,
  check_scheme_id int  null,
  organization_id int  null,
  hospital_structure_id int  null,
  organization_cal_method int  null,
  serve_item_code nvarchar(50) null,
  number decimal(18,4)  null,
  price decimal(18,4)  null,
  cost_item_code nvarchar(50) null,
  direct_cost decimal(28,12)  null,
  indirect_cost_bk decimal(28,12)  null,
  indirect_cost_gl decimal(28,12)  null,
  indirect_cost_yf decimal(28,12)  null,
  medical_cost_type int  null,
  create_user_code nvarchar(30) null,
  create_time datetime  null,
  update_user_code nvarchar(30) null,
  update_time datetime  null,
  delete_flag tinyint  null,
  sink_cost decimal(28,12)  null,
  cooperation_cost decimal(28,12)  null,
  cost_sum decimal(28,12)  null,
  transfer_sign tinyint  null,
  transfer_organization_id int  null,
  sink_hr decimal(28,12)  null,
  sink_dev decimal(28,12)  null
) on pro_crd_scheme(check_scheme_id);

create nonclustered index idx_pro_cost_cal_res_detail
on cost.pro_cost_cal_res_detail_new(id);

EXEC sp_addextendedproperty
'MS_Description', N'主键',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'id'
;

EXEC sp_addextendedproperty
'MS_Description', N'项目成本核算主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'cal_res_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算方案主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'check_scheme_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算科室id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'organization_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'院区id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'hospital_structure_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算科室采用核算方法id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'organization_cal_method'
;

EXEC sp_addextendedproperty
'MS_Description', N'服务项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'serve_item_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'服务项目数量',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'number'
;

EXEC sp_addextendedproperty
'MS_Description', N'服务项目单价',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'price'
;

EXEC sp_addextendedproperty
'MS_Description', N'成本项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'cost_item_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'直接成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'direct_cost'
;

EXEC sp_addextendedproperty
'MS_Description', N'间接成本_本科间接',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'indirect_cost_bk'
;

EXEC sp_addextendedproperty
'MS_Description', N'间接成本_管理分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'indirect_cost_gl'
;

EXEC sp_addextendedproperty
'MS_Description', N'间接成本_医辅分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'indirect_cost_yf'
;

EXEC sp_addextendedproperty
'MS_Description', N'成本维度',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'medical_cost_type'
;

EXEC sp_addextendedproperty
'MS_Description', N'创建人编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'create_user_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'create_time'
;

EXEC sp_addextendedproperty
'MS_Description', N'修改人编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'update_user_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'修改时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'update_time'
;

EXEC sp_addextendedproperty
'MS_Description', N'删除标记:0.存在,1.已删除',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'delete_flag'
;

EXEC sp_addextendedproperty
'MS_Description', N'单位沉没成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'sink_cost'
;

EXEC sp_addextendedproperty
'MS_Description', N'协作成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new',
'COLUMN', N'cooperation_cost'
;

EXEC sp_addextendedproperty
'MS_Description', N'项目成本核算结果明细表',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_res_detail_new'

-- 将原始表的数据导入新的分区表中
SET IDENTITY_INSERT cost.pro_cost_cal_res_detail_new ON;

insert into cost.pro_cost_cal_res_detail_new(
        id, cal_res_id, check_scheme_id, organization_id,
        hospital_structure_id, organization_cal_method, serve_item_code, number,
        price, cost_item_code, direct_cost, indirect_cost_bk,
        indirect_cost_gl, indirect_cost_yf, medical_cost_type, create_user_code,
        create_time, update_user_code, update_time, delete_flag,
        sink_cost, cooperation_cost, cost_sum, transfer_sign,
        transfer_organization_id, sink_hr, sink_dev
       )
select  id, cal_res_id, check_scheme_id, organization_id,
        hospital_structure_id, organization_cal_method, serve_item_code, number,
        price, cost_item_code, direct_cost, indirect_cost_bk,
        indirect_cost_gl, indirect_cost_yf, medical_cost_type, create_user_code,
        create_time, update_user_code, update_time, delete_flag,
        sink_cost, cooperation_cost, cost_sum, transfer_sign,
        transfer_organization_id, sink_hr, sink_dev
from cost.pro_cost_cal_res_detail;

SET IDENTITY_INSERT cost.pro_cost_cal_res_detail_new OFF;

-- 验证数据是否成功导入到新的分区表中（执行一些查询）
-- select count(1) from cost.pro_cost_cal_res_detail_new;

-- 删除原始表并将新的分区表重命名为原始表的名称
drop table cost.pro_cost_cal_res_detail;
exec sp_rename 'cost.pro_cost_cal_res_detail_new', 'pro_cost_cal_res_detail';
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------



-- 1.查看文件组是否存在
select name from sys.filegroups where 1=1 and name = 'pcsi_16';

-- 2.创建文件组
alter database cost_shenzhenrm add filegroup pcsi_16;

-- 3.查看文件是否存在
select filename from sysfiles where name = 'cost_shenzhenrm_pcsi_16_data';

-- 4.创建文件
alter database cost_shenzhenrm add file( 
     name=cost_shenzhenrm_pcsi_16_data, 
	 filename='Q:\深圳人民成本库2024.10.08\20240929\perl_cost\cost_shenzhenrm_pcsi_16_data.ndf', 
	 size=1mb, 
	 maxsize=unlimited, 
	 filegrowth=10mb 
) to filegroup pcsi_16;


-- 5.查询核算方案当前自增值
select isnull(ident_current('cost.pro_check_scheme'), 1) as max_id;

-- 6.查询分区方案中文件组数量
select count(1)
from sys.partition_schemes ps
     inner join sys.destination_data_spaces ds on ds.partition_scheme_id = ps.data_space_id
     inner join sys.filegroups fg on fg.data_space_id = ds.data_space_id
where ps.name = 'pro_crd_scheme'
  and fg.name = 'pcsi_16';
  
-- 7.修改分区方案指定下一个分区文件组
alter partition scheme pro_crd_scheme next used [pcsi_16];

-- 8.查询分区函数值数量
select count(1)
	   <!--
	   pf.name as partition_function_name, pf.function_id, prgv.boundary_id, prgv.value as boundary_value
	   -->
from sys.partition_functions pf
	 inner join sys.partition_range_values prgv on pf.function_id = prgv.function_id
where pf.name = 'pro_crd_part_fun'
  and prgv.value = #{partitionNumber}

-- 9.分区函数拆分
alter partition scheme pro_crd_scheme next used [pcsi_16];
alter partition function pro_crd_part_fun() split range(16);





-- 1.查看所有文件组(包括主文件组)
select * from sys.filegroups;
-- 2.查看文件及文件组的语句
select df.name, df.physical_name, df.size, df.growth,
	   f.name filegroup, f.is_default
from sys.database_files df
     join sys.filegroups f on df.data_space_id = f.data_space_id;


-- 3.查看分区方案
select * from sys.partition_schemes;
-- 4.删除分区方案
drop partition scheme pro_crd_scheme;

--5.查看分区函数
select * from sys.partition_functions;	  
--6.删除分区函数
drop partition function pro_crd_part_fun;


-- 查看数据所在分区序号
select csi.check_scheme_id, 分区序号 = $partition.pro_crd_part_fun(check_scheme_id) 
from cost.pro_cost_cal_res_detail csi
group by csi.check_scheme_id;

-- 查看分区在哪个文件组
select fg.name, 分区号=sp.destination_id
from sys.destination_data_spaces sp
     inner join sys.partition_schemes scm on sp.partition_scheme_id = scm.data_space_id
     inner join sys.filegroups fg on fg.data_space_id = sp.data_space_id
where scm.name='pro_crd_scheme';

-- 查看分区表、分区方案、分区函数等信息
select 
    t.name as table_name,
    s.name as schema_name,
    pf.name as partition_function_name,
    ps.name as partition_scheme_name,
    i.name
from sys.tables t
     join sys.schemas s on t.schema_id = s.schema_id
     join sys.indexes i on t.object_id = i.object_id
     join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
     join sys.partition_functions pf on ps.function_id = pf.function_id;
	 
-- 查看分区函数值
select 
    pf.name as partition_function_name,
    pf.function_id,
    prgv.boundary_id,
    prgv.value as boundary_value
from sys.partition_functions pf
     join sys.partition_range_values prgv on pf.function_id = prgv.function_id
where pf.name = 'pro_crd_part_fun';


-- 查看所有分区方案
select 
    ps.name as partition_scheme_name,
    ps.type_desc as scheme_type,
    ds.destination_id,
    ds.data_space_id,
    fg.name as file_group_name
from sys.partition_schemes ps
     join sys.destination_data_spaces ds on ps.data_space_id = ds.partition_scheme_id
     join sys.filegroups fg on ds.data_space_id = fg.data_space_id
where ps.name = 'pro_csi_scheme';

-- 移动分区到新的文件组
ALTER TABLE YourTable SWITCH PARTITION 2 TO YourTablePartitioned;


-- 7.创建分区函数
declare @num int = 1;
declare @part_id_str nvarchar(max) = '';
declare @part_fun_sql nvarchar(max) = 'create partition function pro_csi_part_fun(int) as range left for values(';
begin

	while @num <= 130
    begin
	   set @part_id_str = concat(@part_id_str, cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @part_id_str = left(@part_id_str, len(@part_id_str) - 1);
	set @part_fun_sql = concat(@part_fun_sql, @part_id_str, ');');

	exec sp_executesql @part_fun_sql;
end;

-- 8.创建分区方案
declare @num int = 1;
declare @scheme_id_str nvarchar(max) = '';
declare @scheme_sql nvarchar(max) = 'create partition scheme pro_csi_scheme as partition pro_csi_part_fun to(';
begin

	while @num <= 131
    begin
	   set @scheme_id_str = concat(concat(@scheme_id_str, 'pcsi_'), cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @scheme_id_str = left(@scheme_id_str, len(@scheme_id_str) - 1);
	set @scheme_sql = concat(@scheme_sql, @scheme_id_str, ');');

	exec sp_executesql @scheme_sql;
end;

